Behavior of Aggregate Functions with NULL Values in MySQL
Aggregate functions in MySQL (such as AVG(), SUM(), COUNT(), MAX(), MIN()) have clearly defined behavior when NULL values are present. In general, NULL values are ignored in aggregation — except for COUNT(*).
AVG() ignores NULLs and computes the average of only non-NULL values.
SUM() ignores NULLs and adds only non-NULL values.
MAX() and MIN() ignore NULLs and return the highest/lowest non-NULL value.
COUNT(expr) ignores NULLs and counts only non-NULL values.
COUNT(*) counts every row, including those containing NULL.
Result:
AVG(val) → (10 + 20) / 2 = 15
SUM(val) → 30
MAX(val) → 20
COUNT(val) → 2 (NULL not counted)
COUNT(*) → 3 (every row counted)
AVG() returns NULL
SUM() returns NULL
MAX() and MIN() return NULL
COUNT(expr) returns 0
COUNT(*) returns number of rows
In summary: Aggregate functions (except COUNT(*)) ignore NULL values. Only non-NULL values contribute to the calculation, and when all inputs are NULL, most aggregates return NULL.